{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 设置sqlserver主/镜像服务器数据库实例 \n",
    " \n",
    "  请*严格按顺序*依次执行下面的代码单元格以设置主服务器和镜像服务器。完成设置后，可以参考下面的文档，该文档简单解释了如何进一步完成一个数据库的镜像过程，并且当主服务器发生问题时，如何处理使得镜像服务器上线。最后探讨了镜像服务器机制的优点和缺点，及适合的应用环境。\n",
    "  \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 步骤0 连接主/镜像服务器数据库实例\n",
    "\n",
    "如果目录下存在connect.json文件，将使用该文件里的连接信息，否则请输入连接信息，输入后连接信息将保存到该文件，供下次使用"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pymssql\n",
    "import json\n",
    "import os\n",
    "import sys\n",
    "from helper import *\n",
    "\n",
    "#\n",
    "# 连接文件名\n",
    "CONNECT_CONFIG_FILE: str = 'connect.json'\n",
    "\n",
    "#\n",
    "# 连接主/镜像服务器\n",
    "#\n",
    "\n",
    "#\n",
    "# 如果配置文件存在，则使用配置文件连接服务器\n",
    "if os.path.isfile(CONNECT_CONFIG_FILE):\n",
    "    answer = None\n",
    "    while answer not in ('y', 'Y', 'n', 'N','a','A',''):     \n",
    "        answer = input(\n",
    "            f'配置文件{CONNECT_CONFIG_FILE}已存在,是否使用该文件连接主/镜像服务器?(y[es]/n[o]/a[bort])')\n",
    "    if answer in ('a','A'):\n",
    "        exit()\n",
    "    if answer in ('y', 'Y',''):\n",
    "        connect_master, connect_mirror = connect_from_json(CONNECT_CONFIG_FILE)\n",
    "    else:\n",
    "        connect_master, connect_mirror = manual_connect()\n",
    "\n",
    "else:\n",
    "    #\n",
    "    # 手动连接\n",
    "    connect_master, connect_mirror = manual_connect()\n",
    "\n",
    "if connect_master is None or connect_mirror is None:\n",
    "    raise ConnectionError('没有主服务器和镜像服务器的连接信息')\n",
    "else:\n",
    "    #\n",
    "    # 正常连接则保存连接信息，供下次使用\n",
    "    save_connect(connect_master=connect_master,\n",
    "                 connect_mirror=connect_mirror, json_file=CONNECT_CONFIG_FILE)\n",
    "\n",
    "print(f'已保存主/镜像服务器数据库实例连接信息,请继续执行下一步')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 步骤1 主服务器数据库实例的出站设置\n",
    "\n",
    "运行下面的代码，根据提示回答问题，收集信息后将产生sql脚本\n",
    "以主服务器管理员登录，运行该sql脚本"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# 主服务器出站设置\n",
    "#\n",
    "try:\n",
    "    with connect_master:\n",
    "        with connect_mirror:\n",
    "\n",
    "            #\n",
    "            # step1. 主服务器的出站设置\n",
    "            master_cert_file_path = master_outbound_setup(connect_master)           \n",
    "except NameError:\n",
    "    print(f'没有连接主服务器和镜像服务器,请执行步骤0')\n",
    "except pymssql.OperationalError as e:\n",
    "    print(f'连接数据库实例失败!')\n",
    "    raise e"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 步骤2 镜像服务器数据库实例的出站设置\n",
    "\n",
    "运行下面的代码，根据提示回答问题，收集信息后将产生sql脚本\n",
    "以镜像服务器管理员登录，运行该sql脚本"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# 镜像服务器出站设置\n",
    "#\n",
    "try:\n",
    "    with connect_master:\n",
    "        with connect_mirror:\n",
    "            #\n",
    "            # step2. 镜像服务器的出站设置\n",
    "            mirror_cert_file_path = mirror_outbound_setup(connect_mirror)            \n",
    "except NameError:\n",
    "    print(f'没有连接主服务器和镜像服务器,请执行步骤0')\n",
    "except pymssql.OperationalError as e:\n",
    "    print(f'连接数据库实例失败!')\n",
    "    raise e"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 步骤3 主服务器数据库实例的入站设置\n",
    "\n",
    "运行下面的代码，根据提示回答问题，收集信息后将产生sql脚本\n",
    "以主服务器管理员登录，运行该sql脚本"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# 主服务器入站设置\n",
    "#\n",
    "try:\n",
    "    with connect_master:\n",
    "        with connect_mirror:      \n",
    "            #\n",
    "            # step3. 主服务器的入站设置 \n",
    "            master_inbound_setup(connect_master, 'c:\\cer\\mirror_cert.cer')            \n",
    "except NameError:\n",
    "    print(f'没有连接主服务器和镜像服务器,请执行步骤0')\n",
    "except pymssql.OperationalError as e:\n",
    "    print(f'连接数据库实例失败!')\n",
    "    raise e"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 步骤4 镜像服务器数据库实例的入站设置\n",
    "\n",
    "运行下面的代码，根据提示回答问题，收集信息后将产生sql脚本\n",
    "以镜像服务器管理员登录，运行该sql脚本"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# 镜像服务器入站设置\n",
    "#\n",
    "try:\n",
    "    with connect_master:\n",
    "        with connect_mirror:      \n",
    "            #\n",
    "            # step4. 镜像服务器的入站设置 \n",
    "            mirror_inbound_setup(connect_master, 'c:\\cer\\master_cert.cer')\n",
    "           \n",
    "except NameError:\n",
    "    print(f'没有连接主服务器和镜像服务器,请执行步骤0')\n",
    "except pymssql.OperationalError as e:\n",
    "    print(f'连接数据库实例失败!')\n",
    "    raise e"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "以上四个步骤全部执行完毕后，主服务器和镜像服务器应该可以在非windows域环境下执行数据库镜像操作了。\n",
    "\n",
    "下面简单说一下如何执行一个数据库的镜像\n",
    "\n",
    "## 数据库镜像的一般步骤 ##\n",
    "\n",
    "1. 在主服务器上需要镜像的数据库，确保处于“完整”模式。(数据库右键->选项->恢复模式里检查)\n",
    "\n",
    "2. 对该数据库进行一次完整备份，产生一个完整备份文件；\n",
    "\n",
    "3. 对该数据库进行一次日志备份，产生一个日志备份文件;\n",
    "\n",
    "4. 将两个备份文件拷贝到镜像服务器，先用完整备份执行一次数据库还原，然后用日志备份对该数据库执行一次日志还原。还原使用的数据库名必须与主服务器上的数据库 *名称一致*，并且两次还原中必须选择 *“with no recovery\"* 选项\n",
    "\n",
    "5. 此时镜像服务器上的还原后的同名数据库应该是处于*不可访问*状态，并显示 *“正在还原\"*\n",
    "\n",
    "6. 在主服务器上选择该数据库，然后右键选择“任务”->\"镜像\"，出现镜像对话框\n",
    "\n",
    "7. 在镜像对话框点击“配置安全性”。由于我们使用高性能模式，所以不需要配置见证服务器，跳过配置见证服务器。如果一切配置正常，在\"主体服务器实例\"(即主服务器)页，应该可以看到信息已自动填好，可以直接点下一步.在\"镜像服务器实例\"页，则需要点击\"连接\",填写镜像服务器连接信息，连接正确后，进入下一步“服务账号\",这一页留空不需要填写，点击下一步完成。\n",
    "   \n",
    "   * 注意:有时通过界面设置镜像数据库会失败，尽管检查发现网络设置没有问题，镜像服务器地址和端口是可以访问的，此时可以在主服务器上通过手工执行下面的sql代码:\n",
    "      ```\n",
    "      ALTER DATABASE XXX SET PARTNER='TCP://[网络地址]:[端口]'\n",
    "      ALTER DATABASE XXX SET PARTNER SAFETY OFF\n",
    "      ```\n",
    "      以上代码手工设置镜像数据库并且置为“高性能模式\". *  \n",
    "      \n",
    "\n",
    "8. 此时会询问是否开始镜像，点击“不开始镜像”，回到镜像对话框页，在运行模式选项里选择“高性能(异步)”，在服务器网络地址部分，填写下主体服务器，镜像服务器的网络地址(是的，必须再重新填写一次，如果不正确的话)\n",
    "\n",
    "9. 点击“点击开始镜像”。如果一切正确，此时主服务器上的数据库处于可访问状态，并且显示“主体，正在镜像\"\n",
    "\n",
    "## 数据库镜像灾变的处理 ##\n",
    "\n",
    "  当主服务器发生灾变，变得不可用，如果是高安全模式，那么镜像服务器的镜像数据库是可以自动成为主数据库（通过见证服务器），并且如果ado.net的连接字符串设置了镜像数据库的话，客户程序甚至可以自动切换到镜像数据库，原先的主数据库如果重新上线的话，将自动成为镜像数据库。这就是所谓的角色互换。\n",
    "\n",
    "  但我们这里是高性能模式，角色互换不会自动发生，需要手工进行。如果主数据库尚可连接，那么在主数据库的镜像对话框里点击“手动故障转移”或者SQL代码:\n",
    "  ```\n",
    "  ALTER DATABASE xxx SET PARTNER FAILOVER;\n",
    "  GO\n",
    "  ```\n",
    "  如果主数据库已不可访问，那么登录镜像数据库执行强制故障转移：\n",
    "  ```\n",
    "  ALTER DATABASE xxx SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS\n",
    "  ```\n",
    "\n",
    "  正确的话镜像数据库将变得可用，此时需要手动修改各程序的连接字符串。如果原主数据库上线，那么将自动变成镜像数据库。\n",
    "\n",
    "  * 如果显示正在“正在恢复...”,尝试:\n",
    "  ```\n",
    "  RESTORE DATABASE xxx with recovery;\n",
    "  ```\n",
    "\n",
    "  如果显示“由于文件不可访问，或者内存或磁盘空间不足，所以无法打开数据库xxx”,尝试重启sqlserver实例，然后使用上面的”Restore”语句，再尝试重建镜像。\n",
    "\n",
    "\n",
    "## 数据库镜像的优缺点 ##\n",
    "\n",
    "数据库镜像优点是任何主体数据库的改变都会实时的同步到镜像数据库，当主体数据库发生灾难时，镜像数据可以自动切换上线，从而提供了极高的数据库安全性和可用性。\n",
    "\n",
    "缺点在于镜像机制要求镜像的数据库必须处于“完整”日志恢复模式。在这种模式下，会记录数据库一切改变的操作到该数据库的日志文件，当数据库如果日常频繁的出现大批量数据的改变（比如批量的数据插入)将会导致对应的日志文件文件快速增长，很容易短时间内耗尽服务器硬盘空间。\n",
    "所以数据库镜像这种机制似乎比较适合日常查询较多，而数据大规模的变化不大的情形。\n",
    "\n",
    "毕竟每次由于空间耗尽，而必须重做镜像还是有些麻烦的。如果必须使用数据库镜像而服务器空间不是很充足的话，需要创建SQLSERVER的维护任务，定时对镜像的数据库进行一次完整备份，一次日志备份(这个操作不会影响已有的镜像)，并且自动删去过期的备份文件，然后使用SQL执行系统日志清理,这种方案既可以使用镜像机制又可以保证磁盘空间不会迅速变满。"
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "323c01e89aeaec088552b1e377b1bae5c9befa83a72dfcdb8a9305725b672942"
  },
  "kernelspec": {
   "display_name": "Python 3.9.7 ('base')",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
